How to Write a Query to Get Top N Records per Group in SQL Server?

Question

Ravi Vishwakarma · Answer

To get the top 3 highest-paid employees in each department from the
Employee table in SQL Server, you can use the ROW_NUMBER() window function along with a
Common Table Expression (CTE) or a 
subquery.and 
Here is an example query:
Using CTE
WITH RankedEmployee AS (
	select 
		*,
		ROW_NUMBER() OVER(PARTITION BY DepartMentID Order BY Salary DESC) AS RowNum
	from Employees
)
select * from RankedEmployee
WHERE 
    RowNum andlt;= 3
Explanation:
CTE Definition: The WITH RankedEmployees AS (...) part defines a Common Table Expression (CTE) named
RankedEmployees.ROW_NUMBER() Function: Inside the CTE, the ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) assigns a unique rank to each employee within their department, ordered by salary in descending order.Selecting Top 3: The SELECT statement outside the CTE filters the results to include only those rows where
RowNum is less than or equal to 3, effectively selecting the top 3 highest-paid employees in each department.
Using Subquery
Alternatively, you can achieve the same result using a subquery:
SELECT 
    *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum
    FROM 
        Employees
) AS RankedEmployees
WHERE 
    RowNum andlt;= 3
Read more
Help with Writing a Subquery to Get Aggregate Data in SQL Server
What is the order of execution process of subquery in SQL?
Can we use ORDER BY in subquery in SQL?
Explain the difference between a subquery and a join in SQL Server.

forum

How to Write a Query to Get Top N Records per Group in SQL Server?

Anubhav Kumar

Can you answer this question?

1 Answers

Using CTE

Using Subquery

Liked By